﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using DevExpress.Web.ASPxEditors;

public partial class admincp_tktlkscl : AdminPage
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Page_PreRender(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.FillDataToDropDownListNamHoc();
            this.FillDataToDropDownListHocKy();
            this.FillDataToDropDownListKhoiLop();
            this.FillDataToDropDownListMonHoc();
            this.FillDataToDropDownListKyKhaoSat();
        }
    }

    private void FillDataToDropDownListNamHoc()
    {
        ddlNamHoc.DataSource = db.NamHocs.Where(nh => nh.TrangThai == true);
        ddlNamHoc.DataTextField = "TenNamHoc";
        ddlNamHoc.DataValueField = "MaNamHoc";
        ddlNamHoc.DataBind();
        ddlNamHoc.Items.Insert(0, "(Xin vui lòng chọn năm học)");
    }

    private void FillDataToDropDownListKhoiLop()
    {
        ddlKhoi.DataSource = db.KhoiLops.Where(kl => kl.TrangThai == true);
        ddlKhoi.DataTextField = "TenKhoiLop";
        ddlKhoi.DataValueField = "MaKhoiLop";
        ddlKhoi.DataBind();
        ddlKhoi.Items.Insert(0, "(Xin vui lòng chọn khối)");
    }

    private void FillDataToDropDownListMonHoc()
    {
        ddlMonHoc.DataSource = db.MonHocs.Where(mh => mh.TrangThai == true);
        ddlMonHoc.DataTextField = "TenMonHoc";
        ddlMonHoc.DataValueField = "MaMonHoc";
        ddlMonHoc.DataBind();
        ddlMonHoc.Items.Insert(0, "(Xin vui lòng chọn môn học)");
    }

    private void FillDataToDropDownListHocKy()
    {
        ddlHocKy.Items.Clear();
        ddlHocKy.Items.Insert(0, "(Xin vui lòng chọn học kỳ)");
        ddlHocKy.Items.Insert(1, "Học kỳ I");
        ddlHocKy.Items.Insert(2, "Học kỳ II");
    }

    private void FillDataToDropDownListKyKhaoSat()
    {
        ddlKyKhaoSat.Items.Clear();
        ddlKyKhaoSat.Items.Insert(0, "(Xin vui lòng chọn kỳ khảo sát)");
        ddlKyKhaoSat.Items.Insert(1, "Khảo sát chất lượng lần I");
        ddlKyKhaoSat.Items.Insert(2, "Khảo sát chất lượng lần II");
        ddlKyKhaoSat.Items.Insert(3, "Khảo sát chất lượng lần III");
    }

    private void FillDataToListBoxChonLopThongKe()
    {
        try
        {
            var lophoc = from lh in db.LopHocs
                         where lh.MaKhoiLop == ddlKhoi.SelectedValue
                         select lh;
            lstLopHocChonThongKe.DataSource = lophoc;
            lstLopHocChonThongKe.TextField = "TenLop";
            lstLopHocChonThongKe.ValueField = "MaLop";
            lstLopHocChonThongKe.DataBind();
        }
        catch
        {
            lstLopHocChonThongKe.DataSource = null;
            lstLopHocChonThongKe.DataBind();
        }
    }
    protected void ddlKhoi_SelectedIndexChanged(object sender, EventArgs e)
    {
        this.FillDataToListBoxChonLopThongKe();
    }

    protected void TongHopDuLieu(object sender, EventArgs e) //Tổng hợp dữ liệu và xuất ra file excel
    {
        //Kiểm tra dữ liệu lọc đầu vào có hợp lệ hay không
        if (ddlKhoi.SelectedIndex < 1 || ddlMonHoc.SelectedIndex < 1 || ddlNamHoc.SelectedIndex < 1 || ddlHocKy.SelectedIndex < 1 || ddlKyKhaoSat.SelectedIndex < 1)
        {
            //Báo lỗi dữ liệu đầu vào không hợp lệ
            ClientScriptManager script = Page.ClientScript;
            if (!script.IsClientScriptBlockRegistered(this.GetType(), "Alert"))
                script.RegisterClientScriptBlock(this.GetType(), "Alert", "<script type=text/javascript>alert('Xin vui lòng chọn đầy đủ thông tin')</script>");
        }
        else
        {
            //Kiểm tra xem khối lớp đã chọn có tồn tại lớp học nào hay không, nếu không có thì báo lỗi
            if (lstLopHocChonThongKe.Items.Count <= 0)
            {
                //Báo lỗi không có lớp tồn tại trong khối lớp đã chọn
                ClientScriptManager script = Page.ClientScript;
                if (!script.IsClientScriptBlockRegistered(this.GetType(), "Alert"))
                    script.RegisterClientScriptBlock(this.GetType(), "Alert", "<script type=text/javascript>alert('Không có lớp học tồn tại trong khối lớp đã chọn')</script>");
            }
            else //Nếu không thì dữ liệu đầu vào hoàn toàn hợp lệ
            {
                using (ExcelPackage package = new ExcelPackage())
                {
                    ExcelWorksheet ws = package.Workbook.Worksheets.Add("TKTLKSCL");

                    /****************************************************************
                     *      BẮT ĐẦU ĐỊNH DẠNG PHẦN HEADER CHO FILE EXCEL            *
                     ****************************************************************/
                    ws.Cells["A1"].Value = "TRƯỜNG THPT TRƯỜNG CHINH";
                    ws.Cells["A1"].Style.Font.Bold = true;
                    ws.Cells["F1"].Value = "THỐNG KÊ TỶ LỆ ĐiỂM KSCL ĐẦU NĂM";
                    ws.Cells["M1"].Value = ddlKhoi.SelectedItem.Text;
                    ws.Cells["G2"].Value = ddlNamHoc.SelectedItem.Text;
                    String LopKhongChonThongKe = "";
                    foreach (ListEditItem li in lstLopHocKhongChonThongKe.Items)
                    {
                        LopKhongChonThongKe += li.Value.ToString() + ",";
                    }
                    ws.Cells["M2"].Value = String.IsNullOrEmpty(LopKhongChonThongKe) == false ? "(Trừ: " + LopKhongChonThongKe.Substring(0, LopKhongChonThongKe.Length - 1) + ")" : "";
                    ws.Cells["G4"].Value = "MÔN: " + ddlMonHoc.SelectedItem.Text;
                    ws.Cells["G4"].Style.Font.Bold = true;

                    ws.Cells["A6"].Value = "GIÁO VIÊN";
                    ws.Cells["A6"].Style.Font.Bold = true;
                    ws.Cells["A6:A7"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderA6A7 = ws.Cells["A6:A7"].Style.Border;
                    borderA6A7.Bottom.Style = borderA6A7.Top.Style = borderA6A7.Left.Style = borderA6A7.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["A6:A7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["A6:A7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["B6"].Value = "LỚP";
                    ws.Cells["B6"].Style.Font.Bold = true;
                    ws.Cells["B6:B7"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderB6B7 = ws.Cells["B6:B7"].Style.Border;
                    borderB6B7.Bottom.Style = borderB6B7.Top.Style = borderB6B7.Left.Style = borderB6B7.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["B6:B7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["B6:B7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["C6"].Value = "SĨ SỐ";
                    ws.Cells["C6"].Style.Font.Bold = true;
                    ws.Cells["C6:C7"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderC6C7 = ws.Cells["C6:C7"].Style.Border;
                    borderC6C7.Bottom.Style = borderC6C7.Top.Style = borderC6C7.Left.Style = borderC6C7.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["C6:C7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["C6:C7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["D6"].Value = "GIỎI";
                    ws.Cells["D6"].Style.Font.Bold = true;
                    ws.Cells["D6:E6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderD6E6 = ws.Cells["D6:E6"].Style.Border;
                    borderD6E6.Bottom.Style = borderD6E6.Top.Style = borderD6E6.Left.Style = borderD6E6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["D6:E6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["D6:E6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["F6"].Value = "KHÁ";
                    ws.Cells["F6"].Style.Font.Bold = true;
                    ws.Cells["F6:G6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderF6G6 = ws.Cells["F6:G6"].Style.Border;
                    borderF6G6.Bottom.Style = borderF6G6.Top.Style = borderF6G6.Left.Style = borderF6G6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["F6:G6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["F6:G6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["H6"].Value = "TB";
                    ws.Cells["H6"].Style.Font.Bold = true;
                    ws.Cells["H6:I6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderH6I6 = ws.Cells["H6:I6"].Style.Border;
                    borderH6I6.Bottom.Style = borderH6I6.Top.Style = borderH6I6.Left.Style = borderH6I6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["H6:I6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["H6:I6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["J6"].Value = "YẾU";
                    ws.Cells["J6"].Style.Font.Bold = true;
                    ws.Cells["J6:K6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderJ6K6 = ws.Cells["J6:K6"].Style.Border;
                    borderJ6K6.Bottom.Style = borderJ6K6.Top.Style = borderJ6K6.Left.Style = borderJ6K6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["J6:K6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["J6:K6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["L6"].Value = "KÉM";
                    ws.Cells["L6"].Style.Font.Bold = true;
                    ws.Cells["L6:M6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderL6M6 = ws.Cells["L6:M6"].Style.Border;
                    borderL6M6.Bottom.Style = borderL6M6.Top.Style = borderL6M6.Left.Style = borderL6M6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["L6:M6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["L6:M6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["N6"].Value = "VẮNG KT";
                    ws.Cells["N6"].Style.Font.Bold = true;
                    ws.Cells["N6:O6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderN6O6 = ws.Cells["N6:O6"].Style.Border;
                    borderN6O6.Bottom.Style = borderN6O6.Top.Style = borderN6O6.Left.Style = borderN6O6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["N6:O6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["N6:O6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["P6"].Value = "TRÊN TB";
                    ws.Cells["P6"].Style.Font.Bold = true;
                    ws.Cells["P6:Q6"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderP6Q6 = ws.Cells["P6:Q6"].Style.Border;
                    borderP6Q6.Bottom.Style = borderP6Q6.Top.Style = borderP6Q6.Left.Style = borderP6Q6.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["P6:Q6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["P6:Q6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["R6"].Value = "TB ĐIỂM THI";
                    ws.Cells["R6"].Style.Font.Bold = true;
                    ws.Cells["R6:R7"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderR6R7 = ws.Cells["R6:R7"].Style.Border;
                    borderR6R7.Bottom.Style = borderR6R7.Top.Style = borderR6R7.Left.Style = borderR6R7.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["R6:R7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["R6:R7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    ws.Cells["S6"].Value = "GHI CHÚ";
                    ws.Cells["S6"].Style.Font.Bold = true;
                    ws.Cells["S6:S7"].Merge = true;
                    //Setting Top/left,right/bottom borders.
                    var borderS6S7 = ws.Cells["S6:S7"].Style.Border;
                    borderS6S7.Bottom.Style = borderS6S7.Top.Style = borderS6S7.Left.Style = borderS6S7.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells["S6:S7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells["S6:S7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    //Phần Header của số lượng
                    ws.Cells["D7"].Value = ws.Cells["F7"].Value = ws.Cells["H7"].Value = ws.Cells["J7"].Value = ws.Cells["L7"].Value =
                        ws.Cells["N7"].Value = ws.Cells["P7"].Value = "SL";

                    ws.Cells["D7"].Style.Font.Bold = ws.Cells["F7"].Style.Font.Bold = ws.Cells["H7"].Style.Font.Bold = ws.Cells["J7"].Style.Font.Bold = ws.Cells["L7"].Style.Font.Bold =
                       ws.Cells["N7"].Style.Font.Bold = ws.Cells["P7"].Style.Font.Bold = true;

                    ws.Cells["D7"].Style.HorizontalAlignment = ws.Cells["F7"].Style.HorizontalAlignment = ws.Cells["H7"].Style.HorizontalAlignment = ws.Cells["J7"].Style.HorizontalAlignment = ws.Cells["L7"].Style.HorizontalAlignment =
                       ws.Cells["N7"].Style.HorizontalAlignment = ws.Cells["P7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    ws.Cells["D7"].Style.VerticalAlignment = ws.Cells["F7"].Style.VerticalAlignment = ws.Cells["H7"].Style.VerticalAlignment = ws.Cells["J7"].Style.VerticalAlignment = ws.Cells["L7"].Style.VerticalAlignment =
                       ws.Cells["N7"].Style.VerticalAlignment = ws.Cells["P7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    //Phần Header của của tỷ lệ
                    ws.Cells["E7"].Value = ws.Cells["G7"].Value = ws.Cells["I7"].Value = ws.Cells["K7"].Value = ws.Cells["M7"].Value =
                        ws.Cells["O7"].Value = ws.Cells["Q7"].Value = "TL";

                    ws.Cells["E7"].Style.Font.Bold = ws.Cells["G7"].Style.Font.Bold = ws.Cells["I7"].Style.Font.Bold = ws.Cells["K7"].Style.Font.Bold = ws.Cells["M7"].Style.Font.Bold =
                       ws.Cells["O7"].Style.Font.Bold = ws.Cells["Q7"].Style.Font.Bold = true;

                    ws.Cells["E7"].Style.HorizontalAlignment = ws.Cells["G7"].Style.HorizontalAlignment = ws.Cells["I7"].Style.HorizontalAlignment = ws.Cells["K7"].Style.HorizontalAlignment = ws.Cells["M7"].Style.HorizontalAlignment =
                       ws.Cells["O7"].Style.HorizontalAlignment = ws.Cells["Q7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    ws.Cells["E7"].Style.VerticalAlignment = ws.Cells["G7"].Style.VerticalAlignment = ws.Cells["I7"].Style.VerticalAlignment = ws.Cells["K7"].Style.VerticalAlignment = ws.Cells["M7"].Style.VerticalAlignment =
                       ws.Cells["O7"].Style.VerticalAlignment = ws.Cells["Q7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    var borderD7Q7 = ws.Cells["D7:Q7"].Style.Border;
                    borderD7Q7.Bottom.Style = borderD7Q7.Left.Style = borderD7Q7.Right.Style = borderD7Q7.Top.Style = ExcelBorderStyle.Thin;

                    /****************************************************************
                     *      KẾT THÚC ĐỊNH DẠNG PHẦN HEADER CHO FILE EXCEL           *
                     ****************************************************************/


                    /****************************************************************
                     *      BẮT ĐẦU TỔNG HỢP DỮ LIỆU CHO BÁO CÁO XUẤT RA EXCEL      *
                     ****************************************************************/
                    int DongHienTai = 8;
                    int RowStartGroup;
                    var lophoc = from lh in db.LopHocs
                                 where lh.MaKhoiLop == ddlKhoi.SelectedValue && lh.MaNamHoc == ddlNamHoc.SelectedValue
                                 select lh;

                    var giaovien = (from gv in db.PhanLopGiaoViens
                                    where gv.LopHoc.MaKhoiLop == ddlKhoi.SelectedValue && gv.MaMonHoc == ddlMonHoc.SelectedValue
                                    select new
                                    {
                                        MaGiaoVien = gv.MaGiaoVien,
                                        TenGiaoVien = gv.GiaoVien.TenGiaoVien
                                    }).Distinct();
                    foreach (var gv in giaovien)
                    {
                        var lopcuagiaovien = from gvdl in db.PhanLopGiaoViens
                                             where gvdl.MaGiaoVien == gv.MaGiaoVien && gvdl.MaMonHoc == ddlMonHoc.SelectedValue &&
                                             gvdl.LopHoc.MaKhoiLop == ddlKhoi.SelectedValue
                                             select new
                                             {
                                                 MaLopHoc = gvdl.LopHoc.MaLop,
                                                 TenLopHoc = gvdl.LopHoc.TenLop,
                                                 SiSo = gvdl.LopHoc.SiSo
                                             };
                        RowStartGroup = DongHienTai;
                        ws.Cells[DongHienTai, 1].Value = gv.TenGiaoVien;
                        ws.Cells[DongHienTai, 1].Style.Font.Bold = true;
                        foreach (var lh in lopcuagiaovien)
                        {
                            ws.Cells[DongHienTai, 2].Value = lh.TenLopHoc;
                            var bordertmp = ws.Cells[DongHienTai, 2, DongHienTai, 19].Style.Border;
                            bordertmp.Bottom.Style = bordertmp.Top.Style = bordertmp.Left.Style = bordertmp.Right.Style = ExcelBorderStyle.Thin;
                            var bangdiem = from bd in db.BangDiems
                                           where bd.MaMonHoc == ddlMonHoc.SelectedValue && bd.MaLop == lh.MaLopHoc && bd.HocKy == ddlHocKy.SelectedIndex
                                           select bd;

                            ws.Cells[DongHienTai, 3].Value = bangdiem.Count();
                            int SL_TrenTrungBinh = 0, SL_Gioi = 0, SL_Kha = 0, SL_TrungBinh = 0, SL_Yeu = 0, SL_Kem = 0, SL_VangThi = 0;
                            switch (ddlKyKhaoSat.SelectedIndex)
                            {
                                case 1:
                                    SL_Gioi = bangdiem.Where(bd => bd.D1T_1 >= 8).Count();
                                    SL_Kha = bangdiem.Where(bd => bd.D1T_1 >= 6.5 && bd.D1T_1 < 8).Count();
                                    SL_TrungBinh = bangdiem.Where(bd => bd.D1T_1 >= 5 && bd.D1T_1 < 6.5).Count();
                                    SL_Yeu = bangdiem.Where(bd => bd.D1T_1 >= 3.5 && bd.D1T_1 < 5).Count();
                                    SL_Kem = bangdiem.Where(bd => bd.D1T_1 >= 0 && bd.D1T_1 < 3.5).Count();
                                    SL_TrenTrungBinh = bangdiem.Where(bd=>bd.D1T_1 > 5).Count();
                                    SL_VangThi = bangdiem.Where(bd => bd.D1T_1 == null).Count();
                                    break;
                                case 2:
                                    SL_Gioi = bangdiem.Where(bd => bd.D1T_2 >= 8).Count();
                                    SL_Kha = bangdiem.Where(bd => bd.D1T_2 >= 6.5 && bd.D1T_2 < 8).Count();
                                    SL_TrungBinh = bangdiem.Where(bd => bd.D1T_2 >= 5 && bd.D1T_2 < 6.5).Count();
                                    SL_Yeu = bangdiem.Where(bd => bd.D1T_2 >= 3.5 && bd.D1T_2 < 5).Count();
                                    SL_Kem = bangdiem.Where(bd => bd.D1T_2 >= 0 && bd.D1T_2 < 3.5).Count();
                                    SL_TrenTrungBinh = bangdiem.Where(bd => bd.D1T_2 > 5).Count();
                                    SL_VangThi = bangdiem.Where(bd => bd.D1T_2 == null).Count();
                                    break;
                                case 3:
                                    SL_Gioi = bangdiem.Where(bd => bd.D1T_3 >= 8).Count();
                                    SL_Kha = bangdiem.Where(bd => bd.D1T_3 >= 6.5 && bd.D1T_3 < 8).Count();
                                    SL_TrungBinh = bangdiem.Where(bd => bd.D1T_3 >= 5 && bd.D1T_3 < 6.5).Count();
                                    SL_Yeu = bangdiem.Where(bd => bd.D1T_3 >= 3.5 && bd.D1T_3 < 5).Count();
                                    SL_Kem = bangdiem.Where(bd => bd.D1T_3 >= 0 && bd.D1T_3 < 3.5).Count();
                                    SL_TrenTrungBinh = bangdiem.Where(bd => bd.D1T_3 > 5).Count();
                                    SL_VangThi = bangdiem.Where(bd => bd.D1T_3 == null).Count();
                                    break;
                            }

                            ws.Cells[DongHienTai, 4].Value = SL_Gioi;
                            ws.Cells[DongHienTai, 5].Formula = ws.Cells[DongHienTai, 4].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 5].Style.Numberformat.Format = "#,##0.00";

                            ws.Cells[DongHienTai, 6].Value = SL_Kha;
                            ws.Cells[DongHienTai, 7].Formula = ws.Cells[DongHienTai, 6].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 7].Style.Numberformat.Format = "#,##0.00";

                            ws.Cells[DongHienTai, 8].Value = SL_TrungBinh;
                            ws.Cells[DongHienTai, 9].Formula = ws.Cells[DongHienTai, 8].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 9].Style.Numberformat.Format = "#,##0.00";

                            ws.Cells[DongHienTai, 10].Value = SL_Yeu;
                            ws.Cells[DongHienTai, 11].Formula = ws.Cells[DongHienTai, 10].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 11].Style.Numberformat.Format = "#,##0.00";

                            ws.Cells[DongHienTai, 12].Value = SL_Kem;
                            ws.Cells[DongHienTai, 13].Formula = ws.Cells[DongHienTai, 12].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 13].Style.Numberformat.Format = "#,##0.00";

                            ws.Cells[DongHienTai, 14].Value = SL_VangThi;
                            ws.Cells[DongHienTai, 15].Formula = ws.Cells[DongHienTai, 14].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 15].Style.Numberformat.Format = "#,##0.00";

                            ws.Cells[DongHienTai, 16].Value = SL_TrenTrungBinh;
                            ws.Cells[DongHienTai, 17].Formula = ws.Cells[DongHienTai, 16].Address + "*100/" + ws.Cells[DongHienTai, 3].Address;
                            ws.Cells[DongHienTai, 17].Style.Numberformat.Format = "#,##0.00";

                            DongHienTai++;
                        }
                        ws.Cells[RowStartGroup, 1, DongHienTai - 1, 1].Merge = true;
                        ws.Cells[RowStartGroup, 1, DongHienTai - 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.Cells[RowStartGroup, 1, DongHienTai - 1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        var border = ws.Cells[RowStartGroup, 1, DongHienTai - 1, 1].Style.Border;
                        border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
                    }
                    /*************************************************************************************************
                        Response.Clear();
                        Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                        Response.BinaryWrite(epplus.GetAsByteArray());
                        Response.End();
                     * 
                     * Response.Clear() 
                     * Response.BinaryWrite(pck.GetAsByteArray()) 
                     * Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" 
                     * Response.AddHeader("content-disposition", "attachment; filename=BalanceSheet" & iCase & ".xlsx") 
                     * Response.End()
                     *************************************************************************************************/
                    //package.SaveAs(Response.OutputStream);
                    Response.Clear();
                    Response.BinaryWrite(package.GetAsByteArray());
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=TKTLD-KSCL.xlsx");
                    Response.End();
                }
            }
        }
    }
}